import requests, json, configparser, os, time
import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
import weixin, mywx
from get_excel import is_workday, is_working
from apscheduler.schedulers.blocking import BlockingScheduler
from enum import Enum
from sqlalchemy import create_engine
from datetime import datetime, timedelta
from io import BytesIO
from PIL import Image

SQLALCHEMY_DATABASE_URL = "mysql+pymysql://root:yongs81@106.52.146.218:3306/demo"
engine = create_engine(SQLALCHEMY_DATABASE_URL)


class LineName(str, Enum):
    w1 = 'w1'
    w2 = 'w2'
    w4 = 'w4'
    w5 = 'w5'
    w6 = 'w6'
    w7 = 'w7'
    w9 = 'w9'
    w10 = 'w10'
    r290 = 'r290'


workfile = r".\file\{0}月上班时间安排.xlsx".format(datetime.now().month)


def date_and_daynight(end):
    # todo 判定当前是白班还是夜班，返回date（日期），daynight（白班/夜班）
    if 20 > end.hour >= 8:
        daynight = "白班"
        date = "{0}-{1}-{2}".format(end.year, end.month, end.day)
    elif end.hour >= 20:
        date = "{0}-{1}-{2}".format(end.year, end.month, end.day)
        daynight = "夜班"
    elif end.hour < 8:
        yestoday = end - timedelta(days=1)
        date = "{0}-{1}-{2}".format(end.year, yestoday.month, yestoday.day)
        daynight = "夜班"
    return date, daynight


def PLC_status(line):
    '''
    在服务器获取PLC状态
    :param line: 线体名称
    :return:
    PLC:通讯正常则True，通讯异常则False
    db_interval:最后一条数据离采集时的时间间隔
    updatetime:最后一次更新时间
    '''
    url = 'http://106.52.146.218:8080/status/{0}'.format(line)
    r = requests.get(url)
    _, PLC, db_interval, updatetime = r.json()
    updatetime = datetime.strptime(updatetime, "%Y-%m-%dT%H:%M:%S")
    return PLC, int(db_interval), updatetime


def auto_boardcast():
    '''
    微信群自动播报程序
    :return:
    '''
    # 自动播报的时间间隔
    interval = 20
    # 腾讯云IP地址
    url = 'http://106.52.146.218:8080/data/'
    # url = 'http://127.0.0.1:8080/data/'
    # 取得当前时间
    times = 0
    end = datetime.now()  # end = datetime(2020, 5, 4, 0, 0, 0)
    # todo 判定当前是白班还是夜班
    date, daynight = date_and_daynight(end)
    # todo 从服务器取得急停数据
    start = (datetime.now() - timedelta(minutes=interval))
    params = {'start': start.strftime("%Y-%m-%d %H:%M:%S"), 'end': end.strftime("%Y-%m-%d %H:%M:%S")}
    while True:
        r = requests.get(url, params=params)
        if r.status_code == 200:
            data, txt = r.json()
            break
        else:
            times = times + 1
            print('重试第{0}次。。。。'.format(times))
            if times > 3:
                print('获取失败！！')
                break
    # todo 查找有多少个直通数据
    zhitong = get_zhitong(interval, start, end)
    # todo 如果是工作日，而且是工作时间，则发送微信
    for line in LineName:
        # 如果是工作日
        if is_workday(workfile, line, date, daynight):
            # 从服务器中获得PLC的状态
            PLC, db_interval, updatetime = PLC_status(line)
            past_time = (datetime.now() - updatetime).seconds
            # 如果PLC状态正常（能ping通），而且最后一次更新时间在一个小时内
            if PLC:
                # 最后一次更新时间在一个小时内
                if past_time < 3600:
                    print('现在是%s的%s,%s线正在开工！' % (date, daynight, line))
                    print('正在发送%s线的急停微信~~' % line)
                    weixin.send(txt, line)
                    # 环形线则播报直通数据
                    if line in ['w4', 'w5', 'w6', 'w7']:
                        weixin.send(zhitong, line)
                    # 播报节拍数据
                    if line in ['w7']:
                        try:
                            filename = get_jiepai_img(interval, line)
                            to_clip(filename)
                            mywx.sendImg("外一车间急停分析结果")
                            os.remove(filename)
                        except:
                            print("无法取得节拍数据！！")
                else:
                    # 给黄永勤发预警
                    mywx.sendText('黄永勤', '%s线已经有%s分钟无法连通PLC，请检查！' % (line, int(past_time / 60)))
                # 如果取不到数据超过10分钟
                if db_interval > 600:
                    mywx.sendText('黄永勤', '%s线已经有%s分钟组态王取不到数据，请检查！' % (line, int(db_interval / 60)))
            else:
                print('%s线PLC网络不通!!!已经有%s分钟了' % (line, int(past_time / 60)))
        else:
            print('%s线没有开%s的%s' % (line, date, daynight))


def get_count(point, interval, line):
    '''
    获得某个点的一个时间段内的数据
    :param point:点的名称
    :param interval:时间段（分钟），从现在开始倒推
    :param line:线体名称
    :return:返回在数据库内查询的数量
    '''
    end = datetime.now()
    start = (datetime.now() - timedelta(minutes=interval))
    start = start.strftime('%Y-%m-%d %H:%M:%S')
    end = end.strftime('%Y-%m-%d %H:%M:%S')
    sql = "select * from pause where DateTime between '{0}' AND '{1}'AND Line Like '{2}';".format(start, end, line)
    df = pd.read_sql_query(sql, engine)
    data = df.loc[(df["VarName"].str.contains(point)), ["VarName", "AlarmType", "DateTime"]].sort_values(by="DateTime")
    number = data.count()["AlarmType"]
    return number


def get_zhitong(interval, start, end):
    zhitong = {}
    for line in ['w4', 'w5', 'w6', 'w7']:
        # 通过“商检房出口直通”点被按下的次数，来取得商检环线直通的数据
        number = get_count("商检房出口直通", interval, line)
        # 通过对比“”和“”两个点的差值，来取得商检环线空车的数据
        context = '{0}-{1}，{2}线商检房直通兜圈有{3}台'.format(start.strftime("%H:%M"), end.strftime("%H:%M"), line, number)
        zhitong.update({line: context})
    return zhitong


def get_jiepai_img(interval=20, line='w7'):
    '''

    :param interval:
    :param line:
    :return:
    '''
    # 设定时间段
    end = datetime.now()
    start = (datetime.now() - timedelta(minutes=interval))
    start = start.strftime('%Y-%m-%d %H:%M:%S')
    end = end.strftime('%Y-%m-%d %H:%M:%S')
    # 读取数据源
    sql = "select * from pause where DateTime between '{0}' AND '{1}' AND Line Like '{2}';".format(start, end, line)
    df = pd.read_sql_query(sql, engine)
    # 筛选出有“光”电有关的数据
    newdata = df.loc[(df["VarName"].str.contains("光")), ["VarName", "AlarmType", "DateTime"]].sort_values(by="DateTime")
    # 分组
    grouped = newdata.groupby(by="VarName")
    # 建立空DataFrame
    all_pd = pd.DataFrame({})
    # 遍历分组
    for group in grouped.groups:
        pd_1 = grouped.get_group(group).sort_values(by="DateTime")
        # DateTime列每个数据减去前一个数据
        pd_2 = pd_1["DateTime"].diff().dt.seconds
        # 筛选出结果大于0的节拍
        npd = pd_2.loc[pd_2 > 0]
        # 重置索引后存入空DateTime
        all_pd[group] = npd.reset_index(drop=True)
    # w7线专用的列表，取哪些列表，对应哪些节拍
    mylist = ["卤检后第二段链板尾光", "压机滚筒出口光电", "商检入口滚筒最后光电",
              "商检出口移载机光电", "封箱机入口光电", "真空线入口最后光电", "真空线出口移载机光电",
              "线头第一段链板尾光电", "线头第二段链板尾光电", "静音房前链板尾光电", "静音房后外观链板尾光"]
    mycol = ["卤检后第二链板", "压机上线", "商检线入口", "商检线出口", "成品节拍", "真空线入口", "真空线出口",
             "线头第一链板", "线头第二链板", "静音房前链板", "外观段链板"]
    # 更改columns索引，并修改其对应节拍名称
    all_pd2 = all_pd[mylist]
    all_pd3 = all_pd2.rename(columns=dict(zip(mylist, mycol)))
    # 显示中文的设置
    plt.figure(figsize=(9.6, 5.1))
    plt.rcParams['font.sans-serif'] = ['SimHei']
    # 两个柱状图的Y轴数据，分别是节拍平均值和最小值
    y = all_pd3.describe().loc['mean'].round(1)
    y1 = all_pd3.describe().loc['min'].round(1)
    # 柱状图的X轴数据
    x = np.arange(len(all_pd3.describe().columns))
    # 柱状图X轴偏移计算
    total_width, n = 0.8, 2  # 有多少个类型，只需更改n即可
    width = total_width / n
    x = x - (total_width - width) / 2
    # X轴的节拍点名称
    _x = all_pd3.describe().columns
    # 正式绘制2个柱状图
    plt.bar(x, y1, width=width, label='最小节拍', color='red')
    plt.bar(x + width, y, width=width, label='平均节拍', color='deepskyblue')
    # 遍历X、Y轴数据，用于显示柱状图对应的数值
    for a, b in zip(x, y1):
        plt.text(a, b + 0.1, b, ha='center', va='bottom')
    for a, b in zip(x, y):
        plt.text(a + width, b + 0.1, b, ha='center', va='bottom')
    # 设置x轴标签
    plt.xticks(np.arange(len(x)), _x, rotation=270, fontsize=8)
    # 设置标签的位置
    plt.legend(bbox_to_anchor=(1.05, 0), loc=3, borderaxespad=0)  # 防止label和图像重合显示不出来
    # x轴、y轴的标签
    plt.ylabel('节拍')
    plt.xlabel('位置')
    plt.axhline(y=all_pd3.mean()["成品节拍"], ls="--", c="red")  # 添加水平直线
    plt.axhline(y=y1.max(), ls="--", c="green")  # 添加水平直线
    # 设置分辨率、尺寸、标题
    plt.subplots_adjust(top=0.92, bottom=0.22, left=0.12, right=0.8, hspace=0.25, wspace=0.35)
    plt.rcParams['savefig.dpi'] = 500  # 图片像素
    plt.rcParams['figure.dpi'] = 300  # 分辨率
    plt.title("线体节拍对照图")
    # plt.show()
    filename = "image.png"
    plt.savefig(filename)
    plt.close('all')
    return filename


def to_clip(filename):
    '''

    :param filename:
    :return:
    '''
    img = Image.open(filename)
    output = BytesIO()
    img.convert("RGB").save(output, "BMP")
    data = output.getvalue()[14:]
    output.close()
    mywx.setImage(data)


if __name__ == '__main__':
    # scheduler = BlockingScheduler()
    # scheduler.add_job(auto_boardcast, 'cron', minute='0,10,20,30,40,50')
    # scheduler.start()
    pass
